package cn.wgx.commons.util;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class ExcelReader {

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    //重载
    public static List<List<List<String>>> readExcel(String excelFile, int column) {
        return readExcel(new File(excelFile),column);
    }
    /**
     * 读取Excel文件内容
     *
     * @param excelFile 要读取的Excel文件
     * @return 读取结果列表，读取失败时返回null
     */
    public static List<List<List<String>>> readExcel(File excelFile, int column) {
        if (excelFile == null || !excelFile.exists()) {
            log.warn("指定的Excel文件不存在！");
            return null;
        }
        Workbook workbook = null;
        FileInputStream inputStream = null;
        String fileName = excelFile.getAbsolutePath();

        try {
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length());
            // 获取Excel文件

            // 获取Excel工作簿
            inputStream = new FileInputStream(excelFile);
            workbook = getWorkbook(inputStream, fileType);

            List<List<List<String>>> sheetList = new ArrayList<>();
            int n = workbook.getNumberOfSheets();
            for (int i = 0; i < n; i++) {//循环sheet
                Sheet sheetAt = workbook.getSheetAt(i);
                if (sheetAt != null) {
                    int physicalNumberOfRows = sheetAt.getLastRowNum();
                    List<List<String>> rowList = new ArrayList<>();
                    for (int j = 0; j <= physicalNumberOfRows; j++) {//循环每一行数据
                        Row row = sheetAt.getRow(j);
                        if(row != null){
                            List<String> colList = new ArrayList<>();
                            for (int g = 0; g < column; g++) {//循环每一列数据
                                colList.add(convertCellValueToString(row.getCell(g)));//转换所有数据为字符串
                            }
                            if(!colList.isEmpty()){
                                rowList.add(colList);
                            }
                        }
                    }
                    if(!rowList.isEmpty()){
                        sheetList.add(rowList);
                    }
                }
            }

            return sheetList;
        } catch (Exception e) {
            log.warn("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
            return null;
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                log.warn("关闭数据流出错！错误信息：" + e.getMessage());
                return null;
            }
        }
    }


    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     *
     * @param inputStream 读取文件的输入流
     * @param fileType    文件后缀名类型（xls或xlsx）
     * @return 包含文件数据的工作簿对象
     * @throws IOException
     */
    public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
        Workbook workbook = null;
        if (fileType.equalsIgnoreCase(XLS)) {
            workbook = new HSSFWorkbook(inputStream);
        } else if (fileType.equalsIgnoreCase(XLSX)) {
            workbook = new XSSFWorkbook(inputStream);
        }
        return workbook;
    }

    /**
     * 将单元格内容转换为字符串
     *
     * @param cell
     * @return
     */
    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = StringUtils.EMPTY;
        switch (cell.getCellType()) {
            case NUMERIC:   //数字
                Double doubleValue = cell.getNumericCellValue();

                // 格式化科学计数法，取一位整数
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            case STRING:    //字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:   //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK:     // 空值
                break;
            case FORMULA:   // 公式
                returnValue = cell.getCellFormula();
                break;
            case ERROR:     // 故障
                break;
            default:
                break;
        }
        return returnValue;
    }

}
